package com.mes.service.manu;

import com.mes.common.MesPdaConstant;
import com.mes.entity.User;
import com.yy.base.common.dao.Dao;
import com.yy.base.common.service.BaseService;
import com.yy.base.util.StringUtil;
import com.yy.base.util.SystemProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * 人工针车
 */
@Service
public class ManuFacilitiesDataService extends BaseService {

    @Autowired
    private Dao dao;

    /**
     * 获取工单下 人工针车部位
     * @param orderNo
     * @return
     */
    public List<Map<String, Object>> getPositionList(String orderNo) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ejo.emeso_job_order_id ,");
        sql.append("( SELECT ec.\"name\" FROM emes_color ec WHERE ec.emes_color_id = ejom.emes_color_id ) \"color\"," );
        sql.append("( SELECT es.file_path FROM emes_shape es WHERE es.emes_shape_id = ejom.emes_shape_id ) \"image\",");
        sql.append("(SELECT ec.NAME FROM emes_component ec WHERE EXISTS ( SELECT 1 FROM emes_pbom_item T WHERE T.emes_pbom_item_id = epc.emes_pbom_item_id AND T.emes_component_id = ec.emes_component_id ) ) \"name\",");
        sql.append("epc.emes_pbom_item_id AS \"pbom_id\",");
        sql.append("CASE ejozc.status WHEN 1 THEN 1 ELSE 0 END AS \"status\" ");
        sql.append("FROM Emeso_Job_Order ejo ");
        sql.append("LEFT JOIN Emeso_Job_Order_Material ejom ON ejo.emeso_job_order_id = ejom.emeso_job_order_id ");
        sql.append("LEFT JOIN emes_process_component epc ON epc.emes_process_id = ejom.emes_process_id ");
        sql.append("LEFT JOIN emeso_job_order_zc_component ejozc ON ejozc.emes_pbom_item_id = epc.emes_pbom_item_id AND ejozc.emeso_job_order_id = ejo.emeso_job_order_id ");
        sql.append("WHERE ejo.code = ?  ");
        sql.append("AND EXISTS ( SELECT 1 FROM emes_process T,emes_processopr f WHERE T.emes_process_id = ejom.emes_process_id AND T.emes_processopr_id = f.emes_processopr_id AND f.code = '").append(MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_CODE).append("')");

        List<Map<String, Object>> list = dao.query(sql.toString(), orderNo);
        return list;
    }

    /**
     * 查询工单下面历史明细sql KEY
     * @param orderNo
     * @return
     */
    public Map<String, Object> emesoJobOrderKey(String orderNo) {
        List<Map<String, Object>> listHis = new ArrayList<Map<String, Object>>();

        StringBuffer sql = new StringBuffer("select Emes_Dc_Input_Page_id from Emes_Dc_Input_Page where code = ?");
        Map<String,Object> orderMap = dao.queryUnique(sql.toString(), orderNo);
        if (null == orderMap || orderMap.isEmpty())
            throw new RuntimeException("编码[" + orderNo + "]未找到对应的生产采集页面信息！");
        StringBuffer sqlData = new StringBuffer("select * from Emes_Dc_Input_Data  where Emes_Dc_Input_Page_id = ? ");
        Map<String,Object> orderData = dao.queryUnique(sqlData.toString(),orderMap.get("EMES_DC_INPUT_PAGE_ID"));
        return orderData;
    }

    /**
     * 查询工单下面历史明细sql 数据集DATA
     * @param orderData
     * @return
     */
    public List<Map<String, Object>> emesoJobOrderData(Map<String,Object> orderData) {
        StringBuffer sqlListData = new StringBuffer(orderData.get("SQL").toString());
        List<Map<String,Object>> listData = dao.query(sqlListData.toString());
        return listData;
    }

    /**
     * 查询工单下面历史明细sql Column
     * @param orderData
     * @return
     */
    public List<Map<String, Object>> emesoJobOrderColumn(Map<String,Object> orderData) {
        StringBuffer sqlMc = new StringBuffer("select name,column_name  from Emes_Dc_Input_Data_Detail where Emes_Dc_Input_Data_id = ? ");
        List<Map<String,Object>> listMc = dao.query(sqlMc.toString(),orderData.get("EMES_DC_INPUT_DATA_ID").toString());
        return listMc;
    }
    /**
     * 找到上一道工序
     * @param orderNo
     * @return
     */
    public String getPreRouteNo(String orderNo) {
        StringBuffer sql = new StringBuffer("SELECT ep.route_no,epr.code,ep.next_route_no,ep.is_validate ");
        sql.append("FROM emes_process ep,emes_processopr epr ");
        sql.append("WHERE EXISTS (SELECT 1 FROM Emeso_Job_Order_Exe ejoe WHERE EXISTS ( SELECT 1 FROM emeso_job_order ejo WHERE ejo.code = ? AND ejo.emeso_job_order_id = ejoe.emeso_job_order_id ) ");
        sql.append("AND ejoe.emes_process_id = ep.emes_process_id ) AND epr.emes_processopr_id = ep.emes_processopr_id ");
        List<Map<String, Object>> list = dao.query(sql.toString(), orderNo);

        String zcRouteNo = null;
        //是否需要验证上一道工序完工
        String isValidate = null;
        for (Map<String, Object> map: list) {
            if (MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_CODE.equals(map.get("code"))) {
                zcRouteNo = map.get("route_no").toString();
                isValidate = map.get("is_validate").toString();
            }
        }

        StringBuffer preRouteNo = new StringBuffer();
        if (StringUtil.isNotEmpty(isValidate) && "Y".equals(isValidate)) {
            for (Map<String, Object> map: list) {
                if (null != map.get("next_route_no") && zcRouteNo.equals(map.get("next_route_no").toString())) {
                    preRouteNo.append(map.get("route_no")).append(",");
                }
            }

            if (StringUtil.isNotEmpty(preRouteNo.toString())) {
                preRouteNo.deleteCharAt(preRouteNo.length() - 1);
            }

        }

        return preRouteNo.toString();
    }

    /**
     * 校验上一道工序是否完工
     * @param nextRouteNos
     * @param orderNo
     * @return
     */
    public String preRouteFinished(String nextRouteNos, String orderNo) {
        String result = null;
        String[] nextRouteNoArr = nextRouteNos.split(",");

        StringBuffer sql = new StringBuffer();
        sql.append("select ejoe.route_name,ejoe.status ");
        sql.append("from emeso_job_order_exe ejoe , emeso_job_order ejo WHERE ejo.code = ? ");
        sql.append("and ejoe.emeso_job_order_id = ejo.emeso_job_order_id and route_no in (");
        for (String routeNo : nextRouteNoArr) {
            sql.append("'").append(routeNo).append("',");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");

        List<Map<String, Object>> list = dao.query(sql.toString(), orderNo);
        for (Map<String, Object> map : list) {
            if ( !MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_FINISHED.equals(map.get("status")) && !MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_OK.equals(map.get("status"))) {
                result = map.get("route_name").toString();
                break;
            }
        }
        return result;
    }

    /**
     * 获取工艺指导书文件路径
     * @param orderNo
     * @return
     */
    public Map<String, Object> getWworkroutePath(String orderNo) throws Exception {
        /*StringBuffer sql = new StringBuffer();
        sql.append("SELECT ep.workroute FROM emes_process ep,emes_processopr epr ");
        sql.append("WHERE EXISTS (SELECT 1 FROM Emeso_Job_Order_Exe ejoe WHERE EXISTS ( SELECT 1 FROM emeso_job_order ejo WHERE ejo.code = ? AND ejo.emeso_job_order_id = ejoe.emeso_job_order_id ) ");
        sql.append(" AND ejoe.emes_process_id = ep.emes_process_id ) AND epr.emes_processopr_id = ep.emes_processopr_id and epr.code = '").append(MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_CODE).append("'");
*/

        Map<String, Object> map = map = getCurrentRGZC(orderNo);
        if (null == map || map.isEmpty()) {
            throw new Exception("当前工序环节不是处于人工针车工序！");
        }

        String sql = "SELECT ep.workroute FROM emes_process ep where emes_process_id = ?";
        return dao.queryUnique(sql, map.get("emes_process_id"));
    }

    /**
     * 人工针车 部位完工
     * @param emesoJobOrderId
     * @param emesPbomItemId
     */
    public Integer addPositionFinished(String emesoJobOrderId, String emesPbomItemId, String status, User user) {
        StringBuffer sql = new StringBuffer("select * from emeso_job_order_zc_component where emeso_job_order_id = ? and emes_pbom_item_id = ?");
        Map<String, Object> map = dao.queryUnique(sql.toString(), emesoJobOrderId, emesPbomItemId);

        if (null != map && !map.isEmpty()) {

            String updateSql = "update emeso_job_order_zc_component set status = ? where emeso_job_order_zc_component_id = ? ";
            return dao.executeSql(updateSql, 1 - Integer.parseInt(status), map.get("emeso_job_order_zc_component_id"));
        } else {

            String insertSql = " insert into emeso_job_order_zc_component("+
                    " emeso_job_order_zc_component_id,         "+
                    " AD_ORG_ID,                               "+
                    " AD_CLIENT_ID,                            "+
                    " ISACTIVE,                                "+
                    " UPDATED,                                 "+
                    " UPDATEDBY,                               "+
                    " CREATED,                                 "+
                    " CREATEDBY,                               "+
                    " emeso_job_order_id,                      "+
                    " emes_pbom_item_id,                       "+
                    " status                                   "+
                    " ) VALUES ( SYS_GUID(), ?, ?, 'Y', now(), ?, now(), ?, ?, ?, 1)";

            List params = new ArrayList();
            params.add(user.getOrgId());
            params.add(user.getClientId());
            params.add(user.getId());
            params.add(user.getId());
            params.add(emesoJobOrderId);
            params.add(emesPbomItemId);

            return dao.executeSql(insertSql, params.toArray());
        }

    }

    /**
     * 人工针车完工
     * @param orderNo
     * @param user
     * @return
     */
    public Integer addProcessFinished(String orderNo, User user) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT emeso_job_order_exe_id FROM emeso_job_order_exe ejoe WHERE");
        sql.append(" EXISTS ( SELECT 1 FROM emeso_job_order ejo WHERE code = ? AND ejo.emeso_job_order_id = ejoe.emeso_job_order_id ) ");
        sql.append(" AND EXISTS (SELECT 1 FROM emes_process ep ,emes_processopr epr WHERE ep.emes_process_id = ejoe.emes_process_id AND  epr.code = ? AND epr.emes_processopr_id = ep.emes_processopr_id )");

        List<Map<String, Object>> list = dao.query(sql.toString(), orderNo, MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_CODE);
        Integer result = 0;
        for (Map<String, Object> map : list) {
            String updateSql = "update emeso_job_order_exe set status = ? , updated = now(), updatedby = ? where emeso_job_order_exe_id = ?";
            List params = new ArrayList();
            params.add(MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_FINISHED);
            params.add(user.getId());
            params.add(map.get("emeso_job_order_exe_id"));

            result += dao.executeSql(updateSql, params.toArray());
        }

        return result;
    }

    /**
     * 人工针车报工环节
     * @param orderNo
     * @param user
     * @return
     * @throws Exception
     */
    public Integer executeRgzcProcess(String orderNo, User user) throws Exception {
        Map<String, Object> map = getCurrentRGZC(orderNo);

        if (null == map || map.isEmpty()) {
            throw new Exception("当前工序环节不是处于人工针车工序！");
        }

        Integer result ;
        if (null == map.get("Exe_Date_From")) { //人工针车未开工

            String updateSql = "update Emeso_Job_Order_Exe set exe_date_from = now() where emeso_job_order_exe_id = ? ";
            result =  dao.executeSql(updateSql,  map.get("emeso_job_order_exe_id"));
        } else { // 结束当前人工针车环节

            Date exeDateFrom = (Date) map.get("exe_date_from");
            Long exeTime = new Date().getTime() - exeDateFrom.getTime();

            if (Integer.parseInt(SystemProperties.getValue("zcRfidInterval")) * 1000 > exeTime) {
                throw new Exception("人工针车两次扫描最少要间隔" + SystemProperties.getValue("zcRfidInterval") + "秒");
            }

            String updateSql = "update Emeso_Job_Order_Exe set exe_date_to = now(), exe_time = ?,status = ? , updated = now(), updatedby = ? where emeso_job_order_exe_id = ? ";
            List params = new ArrayList();
            params.add(exeTime);
            params.add(MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_FINISHED);
            params.add(user.getId());
            params.add(map.get("emeso_job_order_exe_id"));
            result = dao.executeSql(updateSql, params.toArray());

            // 人工针车工序环节结束，用来前端做是否弹出工艺文件判断
            if (null != result && result > 0) {
                result = 100;
            }
        }

        return result;
    }

    /**
     * 获取当前人工针车工序
     * @param orderNo
     * @return
     */
    public Map<String, Object> getCurrentRGZC(String orderNo) throws Exception {
        // 首先获取当前工单 完成的工序
        StringBuffer successProcessSql = new StringBuffer();
        successProcessSql.append("SELECT emes_process_id FROM Emeso_Job_Order_Exe ejoe WHERE EXISTS ( SELECT 1 FROM emeso_job_order ejo WHERE code = ? AND ejo.emeso_job_order_id = ejoe.emeso_job_order_id ) ");
        successProcessSql.append(" AND ( status = ? OR status = ? )");

        List<Map<String, Object>> successProcessList = dao.query(successProcessSql.toString(), orderNo, MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_FINISHED, MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_OK);

        if (null == successProcessList || successProcessList.size() < 1) {
            throw new Exception("工单未开工！");
        }

        // 再查找工单完成的工序的下一道 工序，而且是人工针车的工序
        StringBuffer rgzcProcessSql = new StringBuffer();
        rgzcProcessSql.append("select emes_process_id from emes_process ep where exists ( ");
        rgzcProcessSql.append(" select 1 from emes_process ep1 where emes_process_id in (");

        for (Map<String, Object> map : successProcessList) {
            rgzcProcessSql.append("'").append(map.get("emes_process_id")).append("',");
        }

        rgzcProcessSql.deleteCharAt(rgzcProcessSql.length() - 1);
        rgzcProcessSql.append(") ");
        rgzcProcessSql.append("and ep1.emes_process_route_id = ep.emes_process_route_id ");
        rgzcProcessSql.append("and ep1.next_route_no = ep.route_no " );
        rgzcProcessSql.append(") and EXISTS ( SELECT 1 FROM emes_processopr epr1 WHERE code = ? AND ep.emes_processopr_id = epr1.emes_processopr_id ) ");

        List<Map<String, Object>> rgzcProcessList = dao.query(rgzcProcessSql.toString(), MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_CODE);

        if (null == rgzcProcessList || rgzcProcessList.size() < 1) {
            throw new Exception("尚未到人工针车工序！");
        }

        // 筛选出未开工的工序，只会有一条记录
        StringBuffer rgzcOrderSql = new StringBuffer();
        rgzcOrderSql.append("SELECT emeso_job_order_exe_id, Exe_Date_From,Exe_Date_To,emes_process_id ");
        rgzcOrderSql.append("FROM Emeso_Job_Order_Exe ejoe WHERE EXISTS ( SELECT 1 FROM emeso_job_order ejo WHERE code = ? AND ejo.emeso_job_order_id = ejoe.emeso_job_order_id ) ");
        rgzcOrderSql.append("and emes_process_id in ( ");
        for (Map<String, Object> map : rgzcProcessList) {
            rgzcOrderSql.append("'").append(map.get("emes_process_id")).append("',");
        }

        rgzcOrderSql.deleteCharAt(rgzcOrderSql.length() - 1);
        rgzcOrderSql.append(") ");
        rgzcOrderSql.append("and status = ?");

        return dao.queryUnique(rgzcOrderSql.toString(), orderNo, MesPdaConstant.ARTIFICIAL_NEEDLE_CAR_CREATE);
    }

}
